package com.example.project.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.project.bean.AccountBean;
import com.example.project.bean.BarChartItemBean;
import com.example.project.bean.ChartItemBean;
import com.example.project.bean.TypeBean;
import com.example.project.bean.UserBean;
import com.example.project.utils.FloatUtils;

import java.util.ArrayList;
import java.util.List;

/*
 * 负责管理数据库的类
 *   主要对于表当中的内容进行操作，增删改查
 * */
public class DBManager {

    private static SQLiteDatabase db;


    /* 初始化数据库对象*/
    public static void initDB(Context context){
        DBOpenHelper helper = new DBOpenHelper(context);  //得到帮助类对象
        db = helper.getWritableDatabase();      //得到数据库对象
    }

    /**
     * 读取数据库当中的数据，写入内存集合里
     *   kind :表示收入或者支出
     * */
    public static List<TypeBean> getTypeList(int kind){
        List<TypeBean>list = new ArrayList<>();
        //读取typetb表当中的数据
        String sql = "select * from typetb where kind = "+kind;
        Cursor cursor = db.rawQuery(sql, null);
//        循环读取游标内容，存储到对象当中
        while (cursor.moveToNext()) {
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int imageId = cursor.getInt(cursor.getColumnIndex("imageId"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind1 = cursor.getInt(cursor.getColumnIndex("kind"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            TypeBean typeBean = new TypeBean(id, typename, imageId, sImageId, kind);
            list.add(typeBean);
        }
        return list;
    }

    //验证登录信息
    //验证登录
    public UserBean login(String username, String password){
        String sql="select * from usertb where name=? and pass=?";
        //Cursor对象有点类似于JDBC中的ResultSet,结果集!使用差不多,提供一下方法移动查询结果的记录指针
        Cursor cursor=db.rawQuery(sql, new String[]{username,password}); //用带占位符的SQL查询操作
        UserBean userBean=null;
        //存在数据才返回true
        if(cursor.moveToFirst()==true){ //指针移动到第一行,成功返回true,也说明有数据
            userBean=new UserBean(  );
            userBean.setId( cursor.getInt( cursor.getColumnIndex( "id" ) ) );
            userBean.setName( cursor.getString( cursor.getColumnIndex( "name" ) ) );
            userBean.setPass( cursor.getString( cursor.getColumnIndex( "pass" ) ) );
            userBean.setHead( cursor.getInt( cursor.getColumnIndex( "head" ) ) );
            userBean.setPhone( cursor.getString( cursor.getColumnIndex( "phone" ) ) );
            cursor.close(); //游标需关闭
            return userBean; //则登录成功
        }
        return userBean; //则登录失败
    }

    //注册用户信息
    public static void insertUserBean(UserBean bean){
        ContentValues values = new ContentValues();
        values.put("name",bean.getName());
        values.put("pass",bean.getPass());
        values.put("phone",bean.getPhone());
        values.put("head",bean.getHead());
        db.insert("usertb",null,values);
    }

    //查询账号是否重复
    public Boolean selectinfo(String name){
        String sql="select * from usertb where name=? ";
        //Cursor对象有点类似于JDBC中的ResultSet,结果集!使用差不多,提供一下方法移动查询结果的记录指针
        Cursor cursor=db.rawQuery(sql, new String[]{name}); //用带占位符的SQL查询操作
        Boolean one=false;
        if(cursor.moveToFirst()==true){ //指针移动到第一行,成功返回true,也说明有数据
            one=true;
            return one;
        }
        return one;
    }


    /*
     * 向记账表当中插入一条元素
     * */
    public static void insertItemToAccounttb(AccountBean bean){
        ContentValues values = new ContentValues();
        values.put("typename",bean.getTypename());
        values.put("sImageId",bean.getsImageId());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("time",bean.getTime());
        values.put("year",bean.getYear());
        values.put("month",bean.getMonth());
        values.put("day",bean.getDay());
        values.put("kind",bean.getKind());
        values.put("mainId",bean.getMainId());
        System.out.println( bean.getsImageId() );
        db.insert("accounttb",null,values);
    }
    /*
     * 获取记账表当中某一天的所有支出或者收入情况
     * */
    public static List<AccountBean> getAccountListOneDayFromAccounttb(int year, int month, int day, int mainId){
        List<AccountBean>list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? and day=? and mainId=? order by id desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "", mainId + ""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, beizhu, money, time, year, month, day, kind , mainId);
            list.add(accountBean);
        }
        return list;
    }

    /**
     * 获取某一天的支出或者收入的总金额   kind：支出==0    收入===1
     * */
        public static float getSumMoneyOneDay(int year,int month,int day,int kind,int mainId){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=? and day=? and kind=? and mainId=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "", kind + "", mainId + ""});
        // 遍历
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /*
     * 获取记账表当中某一月的所有支出或者收入情况
     * */
    public static List<AccountBean> getAccountListOneMonthFromAccounttb(int year, int month, int mainId){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=?  and mainId=? order by id desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", mainId + ""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, beizhu, money, time, year, month, day, kind,mainId);
            list.add(accountBean);
        }
        return list;
    }

    /**
     * 获取某一月的支出或者收入的总金额   kind：支出==0    收入===1
     * */
    public static float getSumMoneyOneMonth(int year,int month,int kind,int mainId){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=? and kind=? and mainId=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + "",mainId + ""});
        // 遍历
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /** 统计某月份支出或者收入情况有多少条  收入-1   支出-0*/
    public static int getCountItemOneMonth(int year,int month,int kind,int mainId){
        int total = 0;
        String sql = "select count(money) from accounttb where year=? and month=? and kind=? and mainId=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + "", mainId + ""});
        if (cursor.moveToFirst()) {
            int count = cursor.getInt(cursor.getColumnIndex("count(money)"));
            total = count;
        }
        return total;
    }
    /**
     * 获取某一年的支出或者收入的总金额   kind：支出==0    收入===1
     * */
    public static float getSumMoneyOneYear(int year,int kind,int mainId){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and kind=? and mainId=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", kind + "", mainId + ""});
        // 遍历
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /*
     * 根据传入的id，删除accounttb表当中的一条数据
     * */
    public static int deleteItemFromAccounttbById(int id){
        int i = db.delete("accounttb", "id=?", new String[]{id + ""});
        return i;
    }
    /**
     * 根据备注搜索收入或者支出的情况列表
     * */
    public static List<AccountBean> getAccountListByRemarkFromAccounttb(String beizhu){
        List<AccountBean>list = new ArrayList<>();
        String sql = "select * from accounttb where beizhu like '%"+beizhu+"%'";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String bz = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            int mainId = cursor.getInt(cursor.getColumnIndex("mainId"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, bz, money, time, year, month, day, kind,mainId);
            list.add(accountBean);
        }
        return list;
    }

    /**
     * 查询记账的表当中有几个年份信息
     * */
    public static List<Integer> getYearListFromAccounttb(){
        List<Integer>list = new ArrayList<>();
        String sql = "select distinct (year) from accounttb order by year asc";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            list.add(year);
        }
        return list;
    }

    /*
     * 删除accounttb表格当中的所有数据
     * */
    public static void deleteAllAccount(){
        String sql = "delete from accounttb";
        db.execSQL(sql);
    }

    /**
     * 查询指定年份和月份的收入或者支出每一种类型的总钱数
     * */
    public static List<ChartItemBean> getChartListFromAccounttb(int year, int month, int kind, int mainId){
        List<ChartItemBean>list = new ArrayList<>();
        float sumMoneyOneMonth = getSumMoneyOneMonth(year, month, kind,mainId);  //求出支出或者收入总钱数
        String sql = "select typename,sImageId,sum(money)as total from accounttb where year=? and month=? and kind=? and mainId=? group by typename " +
                "order by total desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + "", mainId + ""});
        while (cursor.moveToNext()) {
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            float total = cursor.getFloat(cursor.getColumnIndex("total"));
            //计算所占百分比  total /sumMonth
            float ratio = FloatUtils.div(total,sumMoneyOneMonth);
            ChartItemBean bean = new ChartItemBean(sImageId, typename, ratio, total);
            list.add(bean);
        }
        return list;
    }

    /**
     * 获取这个月当中某一天收入支出最大的金额，金额是多少
     * */
    public static float getMaxMoneyOneDayInMonth(int year,int month,int kind,int mainId){
        String sql = "select sum(money) from accounttb where year=? and month=? and kind=? and mainId=? group by day order by sum(money) desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + "", mainId + ""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            return money;
        }
        return 0;
    }

    /** 根据指定月份每一日收入或者支出的总钱数的集合*/
    public static List<BarChartItemBean> getSumMoneyOneDayInMonth(int year, int month, int kind, int mainId){
        String sql = "select day,sum(money) from accounttb where year=? and month=? and kind=? and mainId=? group by day";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + "", mainId + ""});
        List<BarChartItemBean>list = new ArrayList<>();
        while (cursor.moveToNext()) {
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            float smoney = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            BarChartItemBean itemBean = new BarChartItemBean(year, month, day, smoney);
            list.add(itemBean);
        }
        return list;
    }

}
